package view;

//import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import model.Customer;
import model.Person;
//import model.FactoryOrder;

public class CustomerDAO {
    private DBConnect con = null;
    	public CustomerDAO (DBConnect incon)
    	{
    		con = incon;
    		try
    		{
    			con.getConnection().setAutoCommit(false);
    		}
    		catch (SQLException e){}
    	}
    	public Customer CreateCustomer(Person inPerson) throws SQLException			
    	{
    	    PreparedStatement ps = null;

    	    try
    	    {
    	    	 ps=con.getConnection().prepareStatement("insert into TEAM4.Customer (Customer_ID) values(?)");
    	    	 ps.setLong(1, inPerson.getPersonId());
    	    	 ps.executeUpdate();
    	    	  System.out.println("Addition Success");
    	    	 ps.close();
    	    }
    	    catch(SQLException e)
    	    {
    	    	  System.out.println("Error in Create Customer" + e.getSQLState());
    	    	  System.out.println("\nError Code: " + e.getErrorCode());
    	    	  System.out.println("\nMessage: " + e.getMessage());
    	    	  System.exit( 1 );
    	    }
    	    catch(Exception e)
    	    {
    	    	  System.out.println("unknown Error in Create Customer");
    	    	  System.out.println("\nMessage: " + e.getMessage());
    	    	  System.exit( 1 );
    	    }
    	    return null;
    	}
    	public Customer ViewCustomer(Customer inCustomer) throws SQLException	
    	{	
    		StringBuffer CustomerReport = new StringBuffer(500); 
	        Person viewPerson = new Person();
    		PreparedStatement ps = null;
	    	ResultSet rs = null;
	    	
    	    try
    	    {
    	    	 ps=con.getConnection().prepareStatement("SELECT p.Person_Id, p.First_Name, p.Last_Name, " +
    	    			 								"p.Street_Address, p.City, p.State, p.Postal_Code, " + 
    	    			 								"p.Birth_Date, p.Phone, p.Fax " +
    	    											"FROM TEAM4.Person p +" +
    	    											"WHERE p.Person_Id = ? ");
    	    	 ps.setLong(1, inCustomer.getCustomerId());
    	    	 rs = ps.executeQuery();
   
    	    	CustomerReport.append(String.format("%1$-16s %2$-40s %3$-40s %4$-50s %5$-40s %6$-3s %7$-5d %8$-D %9$-11s %10$-11s %\n",
    	    										"Person_ID", "First_Name", "Last_Name", "Street_Address", "City", 
    	    										"State", "Postal_Code", "Birth_Date", "Phone", "Fax")); 
    	    	while(rs.next())
    	        {
    	    		viewPerson.setPersonId(rs.getInt(1));
    	    		viewPerson.setFirstName(rs.getString(2));
    	            viewPerson.setLastName(rs.getString(3));
    	            viewPerson.setStreetAddress(rs.getString(4));
    	            viewPerson.setCity(rs.getString(5));
    	            viewPerson.setState(rs.getString(6));
    	            viewPerson.setPostalCode(rs.getString(7));
    	            viewPerson.setBirthDate(rs.getTimestamp(8));
    	            viewPerson.setPhone(rs.getString(9));
    	            viewPerson.setFax(rs.getString(10));
    	            
    	        String CustomerInfo = String.format
    	        	("%1$-16s %2$-40s %3$-40s %4$-50s %5$-40s %6$-3s %7$-5d %8$-D %9$-11s %10$-11s %\n",
    	            rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
    	            rs.getString(6), rs.getString(7), rs.getTimestamp(8), rs.getString(9), rs.getString(10));
    	        
    	            CustomerReport.append(CustomerInfo);      
    	         }
    	          
    	    	  System.out.println(CustomerReport);
    	          System.out.println("Report Complete");
    	         rs.close();
    	         ps.close();

    	    }
    	    catch(SQLException e)
    	    {
    	    	  System.out.println("Error in View Customer" + e.getSQLState());
    	    	  System.out.println("\nError Code: " + e.getErrorCode());
    	    	  System.out.println("\nMessage: " + e.getMessage());
    	    	  System.exit( 1 );
    	    }
    	    catch(Exception e)
    	    {
    	    	  System.out.println("unknown Error in View Customer");
    	    	  System.out.println("\nMessage: " + e.getMessage());
    	    	  System.exit( 1 );
    	    }
	    	
    		return null;
    	}
    	public boolean UpdateCustomer(Person inPerson) throws SQLException	
    	{
    	      PreparedStatement ps= null;
    	      	  System.out.println("Updating Customer Information...");
    	      
    	    try
    	    {     
    	         ps=con.getConnection().prepareStatement
    	        		 ("UPDATE TEAM4.Person p set p.First_Name = ?, p.Last_Name = ?, " + 
    	        		  "p.Street_Address = ?, p.City = ?, p.State = ?, p.Postal_Code = ? " +
    	        		  "p.Birth_Date = ?, p.Phone = ?, p.Fax = ? WHERE Person_Id = ?");
    	         ps.setLong(1,inPerson.getPersonId());
    	         ps.setString(2,inPerson.getFirstName());
    	         ps.setString(3,inPerson.getLastName());
    	         ps.setString(4,inPerson.getStreetAddress());
    	         ps.setString(5,inPerson.getCity());
    	         ps.setString(6,inPerson.getState());
    	         ps.setString(7,inPerson.getPostalCode());
    	         ps.setDate(8,(Date) inPerson.getBirthDate());
    	         ps.setString(9,inPerson.getPhone());
    	         ps.setString(10,inPerson.getFax());
    	         
    	         ps.executeUpdate();
    	          System.out.println("Update Success");
    	         ps.close();
    	    }
    	    catch(SQLException e)
    	    {
    	          System.out.println("Error in Update Customer" + e.getSQLState());
    	          System.out.println("\nError Code: " + e.getErrorCode());
    	          System.out.println("\nMessage: " + e.getMessage());
    	          System.exit( 1 );
    	    }
    	    catch(Exception e)
    	    {
    	          System.out.println("unknown Error in Update Customer");
    	          System.out.println("\nMessage: " + e.getMessage());
    	          System.exit( 1 );
    	    }

    		return false;
    	}
    	public boolean DeleteCustomer(Customer inCustomer) throws SQLException	
    	{		
    	      PreparedStatement ps= null;
    	      	  System.out.println("Deleting Customer...\n");
    	       try
    	       {
    	         ps=con.getConnection().prepareStatement("DELETE TEAM4.Person WHERE Person_Id = ?");
    	         ps.setLong(1,inCustomer.getCustomerId());
    	         ps.executeQuery();
    	          System.out.println("Deletion Success");
    	         ps.close();
    	       }
    	       catch(SQLException e)
    	       {
    	          System.out.println("Error in Delete Customer" + e.getSQLState());
    	          System.out.println("\nError Code: " + e.getErrorCode());
    	          System.out.println("\nMessage: " + e.getMessage());
    	          System.exit( 1 );          
    	       }
    	       catch(Exception e)
    	       {
    	          System.out.println("unknown Error in Delete Customer");
    	          System.out.println("\nMessage: " + e.getMessage());
    	          System.exit( 1 );  
    	       }

    			return false;
    	}
}

